How can I convert JSON keys to columns and values to rows in BigQuery?

To convert JSON keys to columns and values to rows in BigQuery using SQL, you can use the JSON_EXTRACT_SCALAR and UNNEST functions. Here's an example:

Let's say you have a table called my_table in BigQuery with a column json_data containing JSON data. You want to convert the JSON keys to columns and the corresponding values to rows.

Here's a sample query to achieve this:

SELECT
  key AS column_name,
  value AS row_value
FROM
  `my_table`,
  UNNEST(JSON_EXTRACT_SCALAR(json_data, '$')) AS key WITH OFFSET
JOIN
  UNNEST([JSON_EXTRACT_SCALAR(json_data, '$.' || key)]) AS value WITH OFFSET
ON
  key = value

In this query, JSON_EXTRACT_SCALAR(json_data, '$') extracts the top-level keys from the JSON object, and UNNEST is used to create a row for each key. The key column is joined with the corresponding value column using the WITH OFFSET clause.

The result of this query will give you two columns: column_name and row_value. Each row will represent a key-value pair from the JSON data.

Note: If your JSON data has nested structures, you may need to modify the query accordingly to handle those structures.